<!DOCTYPE html>
<html>
<head>
<title>html5-sqlite</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
*{padding: 0; margin: 0;}
body{padding: 20px;}
li{ list-style: none;}
input[type=button]{padding: 10px; display: inline-block; width: 80px; height: 30px;}
.listTitle{background: #efefef; width: 480px; margin-top: 20px;}
.list{width: 480px;}
.list li{line-height: 28px;}
.listTitle span,.list span{ display: inline-block; text-align: center; width: 120px;}
a.del{display: block;}
.tableList{border-top:1px solid #efefef; border-left:1px solid #efefef; }
.tableList td{border-right:1px solid #efefef; border-bottom:1px solid #efefef; padding: 5px 10px; line-height: 30px;}
</style>

</head>
<body>
	<h1>html5-sqlite</h1><br/>
	<h2>注册</h2><br/>
	<input id="text01" type="text" value="ds"/>
	<input id="text02" type="text" value="ds"/>
	<input id="btn01" type="button" value="注册"/> <br/><br/>
	<h2>登录</h2><br/>
	<input id="text03" type="text" value="ds"/>
	<input id="text04" type="text" value="ds"/>
	<input id="btn02" type="button" value="登录"/>
	<br/><br/>
	<h2>用户列表</h2>
	<div class="listTitle"><li><span>id</span><span>姓名</span><span>密码</span><span>操作</span></li></div>
	<div class="list"></div>
	<br/><br/>
	<h2>删除表数据</h2>
	<input class="btn" id="btn03" type="button" value="删除全部"/>
	<br/><br/>
	<h2>删除表</h2>
	<input class="btn" id="btn04" type="button" value="删除表"/>
	<br/><br/><br/>
	<h2>添加任务</h2>
	<br/>
	<h4>任务标题</h4><br/>
	<input id="taskTit" type="text" size="40"/> <input id="creatTask" type="button" value="创建"/><br/><br/>

	<h4>任务项</h4><br/>
	<select name="" id="taskList">
		<!--<option value="">aaa</option>-->
	</select>
	<br/><br/>
	<div id="write">
		<div class="divinclude">
			<input type="text" class="form-control" id="0" name="inputTaskitem" placeholder="点击新的任务项" size="40"/>
		</div>
	</div>
	<br/>
	<input id="taskAdd" type="button" value="添 加"/>
	<br/><br/>
	<table class="tableList" id="tableList">
		<tr>
			<td>任务标题</td>
			<td>任务项</td>
			<td>是否完成</td>
		</tr>
		<!--<tr>
			<td>任务一</td>
			<td>
				任务项一(1)<br/>
				任务项一(1)<br/>
				任务项一(1)
			</td>
			<td>
				<input checked type="checkbox"/><br/>
				<input checked type="checkbox"/><br/>
				<input checked type="checkbox"/>
			</td>
		</tr>-->
	</table>

	<script src="js/jquery.js"></script>
	<script>
		$(document).ready(function(){
			//创建一个数据库
			var myDB = window.openDatabase('myData','1.0','测试数据库',20000);
			/*if(myDB){
			 alert('创建成功')
			 }*/

			/*tx.executeSql("insert into user (name,password) values(?,?)"
					,[name,password]
					,function(tx, result) {alert('注册成功');}
					,function(error){});*/
			var user_id_count = 0 ;

			//创建user表
			myDB.transaction(function(tx){
				tx.executeSql("create table if not exists user (id real PRIMARY KEY,name text unique,password text)",[]);//AUTOINCREMENT
				tx.executeSql("select max(id) as mid from user",[],function(tx, result) {
					user_id_count = result.rows.item(0)['mid'];
					//console.log(user_id_count);
				});
			});

			//用户列表
			function userList(){
				$('.list').empty();
				myDB.transaction(function(tx){
					tx.executeSql("select * from user",[],function(tx,result){
						for(var i=0;i<result.rows.length;i++){
							var lis = "<li><span>" + result.rows.item(i).id + "</span><span>" + result.rows.item(i).name + "</span><span>"+ result.rows.item(i).password +"</span><span><a uid='" + result.rows.item(i).id + "' class='del' href='#'>删除</a></span></li>";
							//console.log(result.rows.item(i));
							$('.list').append(lis);

							//删除一条数据
							/*$('.del').on('click',function(){
								 var delThis = $('.del').index($(this));
								 console.log(delThis);
								 myDB.transaction(function(tx){
									 tx.executeSql("delete from user where id = " + result.rows.item(delThis).id,[],function(tx,result){
										 alert('删除成功');
									 })
								 })
							 })*/

						}
					})
				});
			}
			userList();
			//注册
			$('#btn01').on('click',function(){
				var name = $('#text01').val();
				var password = $('#text02').val();
				myDB.transaction(function(tx){
					tx.executeSql("select * from user where name='"+name+"'and password='"+password+"'",[],function(tx, result) {

						if(result.rows.length>0){
							alert('用户名已存在');
						}else{
							user_id_count++;
							tx.executeSql("insert into user (id,name,password) values(?,?,?)",[user_id_count,name,password],function(tx, result) {
								alert('注册成功');
								userList();
							},function(tx,error){
								alert('注册失败');
							});
						}
					})
				})
			})

			//登录
			$('#btn02').on('click',function(){
				var name = $('#text03').val();
				var password = $('#text04').val();
				myDB.transaction(function(tx){
					tx.executeSql("select * from user where name='"+name+"'and password='"+password+"'",[],function(tx, result) {
						if(result.rows.length>0){
							alert('登录成功');
						}else{
							alert('登录失败');
						}
					},function(error){
						alert('登录失败');
					})
				})
			})


			//删除一条数据
			$('.list').on('click',function(event){
				var del = event.target;
				if($(del).hasClass('del')){
					var uid = parseInt($(del).attr('uid'));
					console.log(typeof uid);
					myDB.transaction(function(tx){
						tx.executeSql("delete from user where id = " + uid,[],function(tx,result){
							alert('删除成功');
							userList();
						})
					})
				}

			})

			//删除表数据
			$('#btn03').on('click',function(){
				myDB.transaction(function(tx){
					tx.executeSql("delete from user",[],function(tx,result){
						alert('删除成功');
						$('.list').empty();
					})
				})
			});

			//删除表
			$('#btn04').on('click',function(){
				myDB.transaction(function(tx){
					tx.executeSql("drop table user",[],function(tx,result){
						alert('删除成功');
						$('.list').empty();
					})
				})
			});


			//创建任务表
			var task_id_count = 0;
			var task_item_id_count = 0;
			myDB.transaction(function(tx){
				tx.executeSql("create table if not exists task (id real PRIMARY KEY,title text,completed integer)",[],function(tx){})
				tx.executeSql("select max(id) as mid from task",[],function(tx, result) {
					task_id_count = result.rows.item(0)['mid'];
				});

				tx.executeSql("create table if not exists task_item (ti_id real PRIMARY KEY,ti_title text,ti_completed integer,task_id real)",[],function(tx){})
				tx.executeSql("select max(ti_id) as mid from task_item",[],function(tx, result) {
					task_item_id_count = result.rows.item(0)['mid'];
				});
			})

			//添加任务项
			$("[name='inputTaskitem']").click(eventClick);
			function eventClick(){
				$('#write').append('<div class="divinclude"><input type="text" class="form-control"  name="inputTaskitem" size="40"/><span class="glyphicon glyphicon-remove deletelist"  name="taskspan"><a href="##">删除</a></span></div>');
				$(this).unbind('click');
				var taskitem=$($("[name='inputTaskitem']")[$("[name='inputTaskitem']").length-1]);
				taskitem.click(eventClick);
				var uu = taskitem.parent('.divinclude');
				$('.deletelist',uu).click(function(){
					$(this).parent(".divinclude").remove();
					taskitem.click(eventClick);
				});
			};

			$('#creatTask').on('click',function(){
				var taskTitle = $('#taskTit').val();

				//插入任务
				myDB.transaction(function(tx){
					task_id_count++;
					tx.executeSql("insert into task (id,title,completed) values (?,?,?)",[task_id_count,taskTitle,0],function(tx,result){
						alert('插入任务成功')
					})
				});

			});
			//获取任务
			myDB.transaction(function(tx){
				tx.executeSql("select * from task",[],function(tx,result){
					for(var i=0;i<result.rows.length;i++){
						//console.log(result.rows.item(i).id);
						$('#taskList').append("<option value='" + result.rows.item(i).id + "'>" + result.rows.item(i).title + "</option>")
					}

				})
			})

			//插入任务项
			$('#taskAdd').on('click',function(){
				var taskItem = $('.divinclude input');
				var taskItems = [];
				for(var i=0;i<taskItem.length;i++){
					if($(taskItem[i]).val()!==''){
						taskItems.push( $(taskItem[i]).val() );
					};
				}

				insert_task_items(taskItems);
			})

			function insert_task_items(arr){
				var i = arr.shift();

				if(i == undefined){
					return;
				}

				var task_select_id = $('#taskList').val();
				myDB.transaction(function(tx){
					task_item_id_count++;
					//console.log(task_item_id_count);
					console.log(i);
					tx.executeSql("insert into task_item (ti_id,ti_title,ti_completed,task_id) values (?,?,?,?)",[task_item_id_count,i,1,task_select_id],function(tx,result){
						alert('插入任务项成功');
						//console.log(task_select_id);
						insert_task_items(arr);
					})
				})
			}

			//查询任务列表
			$('#taskList').change(function(){
				var task_select_id = $('#taskList').val();
				myDB.transaction(function(tx){
					tx.executeSql("select * from task t left join task_item ti on t.id=ti.task_id where t.id="+task_select_id,[],function(tx,result){
						for(var i=0;i<result.rows.length;i++){
							//var task_select_id_n = parseInt(task_select_id);
							console.log(result.rows.item(i).ti_completed);

							if(result.rows.item(i).ti_completed==0){
								var taskList = '<tr><td>'+result.rows.item(i).title+'</td><td>'+ result.rows.item(i).ti_title +'</td><td><input id='+result.rows.item(i).ti_id +' class="check" type="checkbox"/></td></tr>'
								$('#tableList').append(taskList);
							}else{
								var taskList = '<tr><td>'+result.rows.item(i).title+'</td><td>'+ result.rows.item(i).ti_title +'</td><td><input id='+result.rows.item(i).ti_id +' class="check" checked="checked" type="checkbox"/></td></tr>'
								$('#tableList').append(taskList);
							}
						}
					});
				})
			});

			//设置是否完成
			$('#tableList').on('click',function(event){
				myDB.transaction(function(tx){
				var thisCheck = event.target;
					if($(thisCheck).hasClass('check')){
						$(thisCheck).attr('checked',$(thisCheck).length == $(thisCheck).filter(":checked").length);
						var curId = $(thisCheck).attr('id');
						if($(thisCheck).attr('checked')){
							tx.executeSql("UPDATE task_item set ti_completed='1' WHERE ti_id="+curId,[],function(tx,result){
								alert('设为已完成');
							})
						}else{
							tx.executeSql("update task_item set ti_completed='0' where ti_id="+curId,[],function(tx,result){
								alert('设为未完成');
							})
						}
					}
				})
			})

		})
	</script>
</body>
</html>

